/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.mhsoft.pentamanager.services;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

/**
 *
 * @author MH
 */
public class InternalCode {
    private DBConnectionManagerNew dbManager;

    public DBConnectionManagerNew getDbManager() {
        return dbManager;
    }

    public InternalCode() {
        dbManager=new DBConnectionManagerNew(DBConnectionManagerNew.CONN_TYPE_MYSQL, DB.DB_NAME, "root", DB.BASSWORD);
    }
    
    //ref
    public void addRef(String name, String degree, String branch){
        HashMap<String, String> refHash= new HashMap<String, String>();
        refHash.put(DB.REF_NAME, name);
        refHash.put(DB.REF_DEGREE, degree);
        refHash.put(DB.REF_BRANCH_ID, branch);
        dbManager.insert(DB.TABLE_REF, refHash);
    }
    
    //branch
    public void addBranch(String name){
        HashMap<String, String> branchHash= new HashMap<String, String>();
        branchHash.put(DB.BRANCH_NAME, name);
        dbManager.insert(DB.TABLE_BRANCH, branchHash);
    }
    
    //team
    public void addteam(String name, String degree, String branch){
        HashMap<String, String> teamHash= new HashMap<String, String>();
        teamHash.put(DB.TEAM_NAME, name);
        teamHash.put(DB.TEAM_DEGREE, degree);
        teamHash.put(DB.TEAM_BRANCH_ID, branch);
        dbManager.insert(DB.TABLE_TEAM, teamHash);
    }
    
    //player
    public void addplayer(String name, String regstNo, String birthDate, String team){
        HashMap<String, String> playerHash= new HashMap<String, String>();
        playerHash.put(DB.PLAYER_NAME, name);
        playerHash.put(DB.PLAYER_REG_PIN, regstNo);
        playerHash.put(DB.PLAYER_BIRTHDATE, birthDate);
        playerHash.put(DB.PLAYER_TEAM_ID, team);
        dbManager.insert(DB.TABLE_PLAYER, playerHash);
    }
    
    //admin
    public void addadmin(String name, String job, String birthDate, String team){
        HashMap<String, String> adminHash= new HashMap<String, String>();
        adminHash.put(DB.ADMIN_NAME, name);
        adminHash.put(DB.ADMIN_JOB, job);
        adminHash.put(DB.ADMIN_BIRTHDATE, birthDate);
        adminHash.put(DB.ADMIN_TEAM_ID, team);
        dbManager.insert(DB.TABLE_ADMIN, adminHash);
    }
    
    //Competition
    public void addcomp(String generalType, String type, String date, String teamsNo){
        HashMap<String, String> compHash= new HashMap<String, String>();
        compHash.put(DB.COMP_GENERAL_TYPE, generalType);
        compHash.put(DB.COMP_TYPE, type);
        compHash.put(DB.COMP_DATE, date);
        compHash.put(DB.COMP_TEAMS_NO, teamsNo);
        dbManager.insert(DB.TABLE_COMP, compHash);
    }
    
    //Competition Winner Team
    public void addComptWinner(String comptID, String winnerTeam) {
        HashMap<String, String> compHash= new HashMap<String, String>();
        compHash.put(DB.COMP_CHAMP_TEAM, winnerTeam);
        dbManager.update(DB.TABLE_COMP, compHash, DB.COMP_ID + " = " + comptID);
    }
    
    //match
    public void addmatch(String team1, String team2, String team1Players, String team2Players, String ref, String date, String time, String compt, String playGround){
        HashMap<String, String> matchHash= new HashMap<String, String>();
        matchHash.put(DB.MATCH_TEAM1_ID, team1);
        matchHash.put(DB.MATCH_TEAM2_ID, team2);
        matchHash.put(DB.MATCH_TEAM1_PLAYERS, team1Players);
        matchHash.put(DB.MATCH_TEAM2_PLAYERS, team2Players);
        matchHash.put(DB.MATCH_REFS, ref);
        matchHash.put(DB.MATCH_DATE, date);
        matchHash.put(DB.MATCH_TIME, time);
        matchHash.put(DB.MATCH_COMPITATION_ID, compt);
        matchHash.put(DB.MATCH_PLAYGROUND_ID, playGround);
        dbManager.insert(DB.TABLE_MATCH, matchHash);
//        matchHash.clear();
//        matchHash.put(DB.m, date)
//        dbManager.update(DB.TABLE_MATCH, );
    }
    public String getMatchID(String team1, String team2,String date, String time, String compt, String playGround){
        ArrayList<HashMap<String, String>> matchData= dbManager.select(new String[]{DB.TABLE_MATCH},
            new String[]{"MAX("+DB.MATCH_ID+")"},
            DB.MATCH_TEAM1_ID+" = "+team1
            +" AND "+DB.MATCH_TEAM2_ID+" = "+team2
            +" AND "+DB.MATCH_DATE+" = '"+date+"'"
            +" AND "+DB.MATCH_TIME+" = '"+time+"'"
            +" AND "+DB.MATCH_COMPITATION_ID+" = "+compt
            +" AND "+DB.MATCH_PLAYGROUND_ID+" = "+playGround);
        return matchData.get(0).get("MAX("+DB.MATCH_ID+")");
    }
    
    //goal
    public void addgoal(String match, String team, String player, String time){
        HashMap<String, String> goalHash= new HashMap<String, String>();
        goalHash.put(DB.GOAL_MATCH_ID, match);
        goalHash.put(DB.GOAL_TEAM_ID, team);
        goalHash.put(DB.GOAL_PLAYER_ID, player);
        goalHash.put(DB.GOAL_TIME, time);
        dbManager.insert(DB.TABLE_GOAL, goalHash);
    }
    
    //card
    public void addcard(String cardType, String match, String team, String player, String time){
        HashMap<String, String> cardHash= new HashMap<String, String>();
        cardHash.put(DB.CARD_TYPE_ID, cardType);
        cardHash.put(DB.CARD_MATCH_ID, match);
        cardHash.put(DB.CARD_TEAM_ID, team);
        cardHash.put(DB.CARD_PLAYER_ID, player);
        cardHash.put(DB.CARD_TIME, time);
        dbManager.insert(DB.TABLE_CARD, cardHash);
    }
    
    //branches
    public HashMap<String, String> getBranchesForCompo(){
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_BRANCH}, new String[]{DB.BRANCH_ID, DB.BRANCH_NAME});
        HashMap<String, String> newList=new HashMap<String, String>();
        for (int i = 0; i < data.size(); i++) {
            HashMap<String, String> item=data.get(i);
            newList.put(item.get(DB.BRANCH_ID), item.get(DB.BRANCH_NAME));
        }
        return newList;
    }
    
    //teams
    public HashMap<String, String> getTeamsForCompo(){
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_TEAM}, new String[]{DB.TEAM_ID, DB.TEAM_NAME});
        HashMap<String, String> newList=new HashMap<String, String>();
        for (int i = 0; i < data.size(); i++) {
            HashMap<String, String> item=data.get(i);
            newList.put(item.get(DB.TEAM_ID), item.get(DB.TEAM_NAME));
        }
        return newList;
    }

    //Matches
    public HashMap<String, String> getMatchesForCompo(String comptID){
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_MATCH}, new String[]{DB.MATCH_ID, DB.MATCH_TEAM1_ID,DB.MATCH_TEAM2_ID,DB.MATCH_DATE}, DB.MATCH_COMPITATION_ID + " = " + comptID);
        HashMap<String, String> matchesNewList=new HashMap<String, String>();
        HashMap<String, String> teamsList=new InternalCode().getTeamsForCompo();
        for (int i = 0; i < data.size(); i++) {
            HashMap<String, String> matchTemp=data.get(i);
            matchesNewList.put(matchTemp.get(DB.MATCH_ID), matchTemp.get(DB.MATCH_DATE) + "-" + teamsList.get(matchTemp.get(DB.MATCH_TEAM2_ID)) + "-" + teamsList.get(matchTemp.get(DB.MATCH_TEAM1_ID)));
        }
        return matchesNewList;
    }
    
    //Bring Teams in specific match
    public HashMap<String, String> getTeamsForCompo(String matchID){
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_MATCH}, new String[]{DB.MATCH_TEAM1_ID, DB.MATCH_TEAM2_ID}, DB.MATCH_ID + " = " + matchID);
        HashMap<String, String> TeamTempHash=new HashMap<String, String>();
        TeamTempHash=data.get(0);
        Object[] TeamsIds={TeamTempHash.get(DB.MATCH_TEAM1_ID),TeamTempHash.get(DB.MATCH_TEAM2_ID)};
        
        //insert data of team1 in first index
        ArrayList<HashMap<String, String>> TeamData=dbManager.select(new String[]{DB.TABLE_TEAM}, new String[]{DB.TEAM_ID, DB.TEAM_NAME}, DB.TEAM_ID + " = " + TeamsIds[0].toString());
        HashMap<String, String> TeamNewList=new HashMap<String, String>();
        TeamTempHash=TeamData.get(0);
        TeamNewList.put(TeamTempHash.get(DB.TEAM_ID), TeamTempHash.get(DB.TEAM_NAME));
        
        //insert data of team2 in second index
        TeamData=dbManager.select(new String[]{DB.TABLE_TEAM}, new String[]{DB.TEAM_ID, DB.TEAM_NAME}, DB.TEAM_ID + " = " + TeamsIds[1].toString());
        TeamTempHash=TeamData.get(0);
        TeamNewList.put(TeamTempHash.get(DB.TEAM_ID), TeamTempHash.get(DB.TEAM_NAME));
        
        return TeamNewList;
    }
    
    public HashMap<String, String> getTeamsForCompo(String team1ID, String team2ID){
        ArrayList<HashMap<String, String>> TeamData=dbManager.select(new String[]{DB.TABLE_TEAM}, new String[]{DB.TEAM_ID,DB.TEAM_NAME}, DB.TEAM_ID + " = " + team1ID + " OR "+DB.TEAM_ID + " = " + team2ID);
        HashMap<String, String> teamNewList=new HashMap<String, String>();
        for (int i = 0; i < TeamData.size(); i++) {
            HashMap<String, String> teamTempData=TeamData.get(i);
            teamNewList.put(teamTempData.get(DB.TEAM_ID), teamTempData.get(DB.TEAM_NAME));
        }
        return teamNewList;
    }
    
    //Bring Players of Specific Team in Specific Match
    public HashMap<String, String> getPlayersForCompo(String matchID, int teamNo){
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_MATCH}, new String[]{DB.MATCH_TEAM1_PLAYERS, DB.MATCH_TEAM2_PLAYERS}, DB.MATCH_ID + " = " + matchID);
        HashMap<String, String> tempHash=new HashMap<String, String>();
        tempHash=data.get(0);
//        Object[] TeamsIds={TeamTempHash.get(DB.MATCH_TEAM1_ID),DB.MATCH_TEAM2_ID};
        String Players="";
        //check wether to show players of team1 or team2
        switch(teamNo){
            case 1: Players=tempHash.get(DB.MATCH_TEAM1_PLAYERS);
                break;
            case 2: Players=tempHash.get(DB.MATCH_TEAM2_PLAYERS);
                break;
        }
        ArrayList<HashMap<String, String>> playerData=dbManager.select(new String[]{DB.TABLE_PLAYER}, new String[]{DB.PLAYER_ID, DB.PLAYER_NAME}, DB.PLAYER_ID + " IN ( " + Players + " ) ");
        tempHash.clear();
        HashMap<String, String> playerNewList=new HashMap<String, String>();
        for (int i = 0; i < playerData.size(); i++) {
            tempHash=playerData.get(i);
            playerNewList.put(tempHash.get(DB.PLAYER_ID), tempHash.get(DB.PLAYER_NAME));
        }
        return playerNewList;
    }
    
    public HashMap<String, String> getPlayersForCompo(String teamID){
        ArrayList<HashMap<String, String>> playerData=dbManager.select(new String[]{DB.TABLE_PLAYER}, new String[]{DB.PLAYER_ID, DB.PLAYER_NAME}, DB.PLAYER_TEAM_ID + " = " +teamID);
        HashMap<String, String> playerNewList=new HashMap<String, String>();
        HashMap<String, String> tempHash;
        for (int i = 0; i < playerData.size(); i++) {
            tempHash=playerData.get(i);
            playerNewList.put(tempHash.get(DB.PLAYER_ID), tempHash.get(DB.PLAYER_NAME));
        }
        return playerNewList;
    }
    
    public ResultSet getTeamsForTable() {
//        ArrayList<HashMap<String, String>> data= dbManager.select(new String[]{DB.TABLE_MATCH, DB.TABLE_TEAM+" as 'Team1'", DB.TABLE_TEAM+" as 'Team2'", DB.TABLE_TEAM+" as 'PlayGround'", DB.TABLE_GOAL+" as 'Goal1'", DB.TABLE_GOAL+" as 'Goal2'"}, new String[]{"PlayGround."+DB.TEAM_NAME+" as 'PlayGround'", DB.TABLE_MATCH+"."+DB.MATCH_DATE+"+' - '+"+DB.TABLE_MATCH+"."+DB.MATCH_TIME+" as 'DateTime'", "Team2."+DB.TEAM_NAME+" as 'Team2'", "count(Goal2."+DB.GOAL_ID+") as 'Goal2'", "count(Goal1."+DB.GOAL_ID+") as 'Goal1'", "Team1."+DB.TEAM_NAME+" as 'Team1'"}, "PlayGround."+DB.TEAM_ID+"="+DB.TABLE_MATCH+"."+DB.MATCH_PLAYGROUND_ID+" and Team2."+DB.TEAM_ID+"="+DB.TABLE_MATCH+"."+DB.MATCH_TEAM2_ID+" and Goal2."+DB.GOAL_MATCH_ID+"="+DB.TABLE_MATCH+"."+DB.MATCH_ID+" and Goal2."+DB.GOAL_TEAM_ID+"="+DB.TABLE_MATCH+"."+DB.MATCH_TEAM2_ID+" and Goal1."+DB.GOAL_MATCH_ID+"="+DB.TABLE_MATCH+"."+DB.MATCH_ID+" and Goal1."+DB.GOAL_TEAM_ID+"="+DB.TABLE_MATCH+"."+DB.MATCH_TEAM1_ID+" and Team1."+DB.TEAM_ID+"="+DB.TABLE_MATCH+"."+DB.MATCH_TEAM1_ID);
        ResultSet data= dbManager.selector("SELECT team.tName AS 'PlayGround', match.[mTime] & ' - ' & [mDate] AS 'DateTime', team_2.tName AS 'Team2', COUNT(goal_2.gID) AS 'Goal2', COUNT(goal_1.gID) AS 'Goal1', team_1.tName AS 'Team1' FROM goal AS goal_1 INNER JOIN (goal AS goal_2 INNER JOIN (team AS team_2 INNER JOIN (team AS team_1 INNER JOIN (team INNER JOIN [match] ON team.tID = match.mPlaygroundID) ON team_1.tID = match.mTeam1ID) ON team_2.tID = match.mTeam2ID) ON (goal_2.gTeamID = match.mTeam2ID) AND (goal_2.gMatchID = match.mID)) ON (goal_1.gMatchID = match.mID) AND (goal_1.gTeamID = match.mTeam1ID) GROUP BY team.tName, match.[mTime] & ' - ' & [mDate], team_2.tName, team_1.tName ORDER BY team.tName, match.[mTime] & ' - ' & [mDate], team_2.tName, team_1.tName");
        return data;
    }
    
    public HashMap<String, String> getMatchBasicData(String matchID) {
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_MATCH}, new String[]{DB.MATCH_DATE, DB.MATCH_TIME, DB.MATCH_REFS, DB.MATCH_TEAM1_ID, DB.MATCH_TEAM2_ID, DB.MATCH_COMPITATION_ID, DB.MATCH_PLAYGROUND_ID}, DB.MATCH_ID + " = " +matchID);
        HashMap<String, String> matchBasicData= new HashMap<String, String>();
        matchBasicData=data.get(0);
        return matchBasicData;
    }
    
    public String getTeamName(String teamID) {
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_TEAM}, new String[]{DB.TEAM_NAME}, DB.TEAM_ID + " = " +teamID);
        String teamName=data.get(0).get(DB.TEAM_NAME).toString();
        return teamName;
    }
    
    public HashMap<String, String> getComptName(String comptID) {
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_COMP}, new String[]{DB.COMP_GENERAL_TYPE, DB.COMP_TYPE, DB.COMP_DATE}, DB.COMP_ID + " = " +comptID);
        HashMap<String, String> comptName=new HashMap<String, String>();
        comptName=data.get(0);
        return comptName;
    }
    
    public ResultSet getRefsNames(String refsID) {
        ResultSet data=dbManager.selector("select "+DB.REF_NAME+", "+DB.REF_DEGREE+", "+DB.BRANCH_NAME+" from "+DB.TABLE_REF+" inner join "+DB.TABLE_BRANCH+" on "+DB.REF_BRANCH_ID+"="+DB.BRANCH_ID+" where "+DB.REF_ID+" in("+refsID+")");
        return data;
    }
    
    public ArrayList<HashMap<String, String>> getGoals(String matchID) {
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_GOAL}, new String[]{DB.GOAL_PLAYER_ID, DB.GOAL_TEAM_ID, DB.GOAL_TIME}, DB.GOAL_MATCH_ID+"="+matchID);
        return data;
    }
    
    public ArrayList<HashMap<String, String>> getPunch(String matchID) {
        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_CARD}, new String[]{DB.CARD_PLAYER_ID, DB.CARD_TEAM_ID, DB.CARD_TIME, DB.CARD_TYPE_ID}, DB.CARD_MATCH_ID+"="+matchID);
        return data;
    }
    
    //Return Teams IDs of specific competition
//    public int[] getTeamsOfCompts(String comptID) {
//        ArrayList<HashMap<String, String>> data=dbManager.select(new String[]{DB.TABLE_COMP}, new String[]{DB.COMP_TEAMS_NO}, DB.COMP_ID+" = "+comptID);
//        HashMap<String, String> item=data.get(0);
//        String[] steamsNo=item.get(DB.COMP_TEAMS_NO).split(",");
//        int[] iteamsNo=new int[steamsNo.length];
//        
//        for(int i=0;i<steamsNo.length;i++)
//            iteamsNo[i]=Integer.parseInt(steamsNo[i]);
//        
//        return iteamsNo;
//    }
    
    //Player Data
//    public ResultSet getEmployeeData(String cond){
//        rs=d.selector("employees", "*", cond);
//        return rs;
//    }
//    public ResultSet getEmployeeData(){
//        rs=d.selector("employees", "*");
//        return rs;
//    }
//    public void setPlayerData(String name,String birthdate){
//        d.inserter("player", DB.PLAYER_BIRTHDATE+"^"+DB.PLAYER_NAME, "s#"+name+"33"+"^s#"+birthdate);
//    }
//    public void setAreaData(String area_name,String province){
//        d.inserter("area", "area_name^province", "s#"+area_name+"^s#"+province);
//    }
//    public void modifyEmployeeData(String emp_name,String job_Id,String phone,String mobile,String address,String salary,String id,String cond){
//        d.updater("employees", "emp_name^job_Id^phone^mobile^address^salary^id",
//                "s#"+emp_name+"^i#"+job_Id+"^s#"+phone+"^s#"+mobile+"^s#"+address+"^f#"+salary+"^s#"+id,cond);
//    }
//    void deleteEmployeeData(String cond){
//        d.deleter("employees", cond);
//    }
}
